I´m doing migrations in Laravel and this error happens when I proceed with the command PHP artisan migrate:
In Connection.php line 664:
SQLSTATE[HY000]: General error: 3780 Referencing column 'user_id' and referenced column 'id' in foreign key constraint 'almacen_movimientos_user_id_foreign' are incompatible. (SQL: alter table
almacen_movimientosadd constraintalmacen_movimientos_user_id_foreignforeign key (user_id) referencesusers(id
) on delete restrict)
In PDOStatement.php line 129:
SQLSTATE[HY000]: General error: 3780 Referencing column 'user_id' and referenced column 'id' in foreign key constraint 'almacen_movimientos_user_id_foreign' are incompatible.
My migrations look like this:
almacen_movimientos table
public function up()
{
Schema::create('almacen_movimientos', function (Blueprint $table) {
$table->unsignedBigInteger('id');
$table->integer('cliente_proveedor_id');
$table->integer('empresa_id');
$table->integer('user_id');
$table->enum('tipo' , ['ENTRADA' , 'SALIDA' , 'REUBICACION' , 'TRASPASO' , 'DEVOLUCION' , 'MSRO' , 'ENTRADA POR TRASPASO' , 'SALIDA POR TRASPASO'])->nullable();
$table->string('referencia' , 255)->nullable();
$table->string('observaciones' , 255)->nullable();
$table->timestamp('created_at');
$table->timestamp('updated_at');
$table->timestamp('deleted_at');
$table->string('transportista' , 255)->nullable();
$table->string('operador' , 255)->nullable();
$table->string('procedencia' , 255)->nullable();
$table->integer('almacen_id')->nullable();
$table->foreign('cliente_proveedor_id')->references('id')->on('empresas')->onDelete('restrict');
$table->foreign('empresa_id')->references('id')->on('empresas')->onDelete('restrict');
$table->foreign('user_id')->references('id')->on('users')->onDelete('restrict');
$table->foreign('almacen_id')->references('id')->on('almacenes')->onDelete('restrict');
});
}
Users Table
public function up()
{
Schema::create('users', function (Blueprint $table) {
$table->unsignedBigInteger('id');
$table->string('name' , 255);
$table->string('apellido_paterno' , 115)->nullable();
$table->string('apellido_materno' , 115)->nullable();
$table->dateTime('fecha_nacimiento')->nullable();
$table->string('telefono1' , 10)->nullable();
$table->string('telefono2' , 10)->nullable();
$table->string('calle' , 255)->nullable();
$table->string('numero' , 45)->nullable();
$table->string('colonia' , 255)->nullable();
$table->string('codigo_postal' , 6)->nullable();
$table->string('email' , 255)->unique();
$table->string('user' , 20)->nullable()->unique();
$table->string('password' , 255);
$table->string('palabra_secreta' , 255);
$table->string('remember_token' , 100)->nullable();
$table->unsignedInteger('empresa_id')->nullable();
$table->timestamp('created_at');
$table->timestamp('updated_at');
$table->timestamp('deleted_at');
$table->foreign('empresa_id')->references('id')->on('empresas')->onDelete('restrict');
});
}
Can somebopdy tell me what am I doing wrong? I cannot fix this.
Thank you.
Regards.
In users table you have defined primary key with id as unsigned bigint and in almacen_movimientos table the referenced user_id is defined as int
change
$table->integer('user_id');
to
$table->unsignedBigInteger('user_id');
The structure and data type of PRIMARY KEY and FOREIGN KEY must be same
Make sure that foreign key data type is the same as its referencing column
You can also solve in a more elegant way changing in yout schema of almacen_movimientos you should change:
$table->integer('user_id');
$table->foreign('user_id')->references('id')->on('users')->onDelete('restrict');
with
$table->foreignId('user_id')->constrained()->onDelete('restrict');
Have a look at the documentation, you can do it from Laravel 7 on: https://laravel.com/docs/8.x/migrations#foreign-key-constraints
Follow the dataType to resolve these issues.
public function up(){
Schema::create('posts', function (Blueprint $table) {
$table->bigIncrements('id');
$table->string('body');
$table->unsignedBigInteger('user_id');
$table->timestamps();
$table->foreign('user_id')
->references('id')
->on('users')
->onDelete('cascade');
});
}
Noticed the local column ('id') was set to bigIncrements('id') and the foreign was set to Unsigned big integer.
$table->increments('id')
use unsignedInteger in your foreign key
$table->id()
use unsignedBigInteger in your foreign Key